package com.googlecode.worktools.service.datatruncation;



import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;

import org.apache.commons.lang.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import com.googlecode.worktools.domain.ColumnInfo;
import com.googlecode.worktools.utils.Utils;



/**
 * 数据库连接类（临时用于数据截断分析功能）
 * @author Kobe Su,2009-11-18
 */
public class DBConn {
	private static final Log log = LogFactory.getLog(DBConn.class);
	
	private static final String DRIVER = "net.sourceforge.jtds.jdbc.Driver";
	private static String url;
	private static String userName;
	private static String password;
	private Connection conn = null;
	private ResultSet rs = null;
	private static DBConn dbconn = new DBConn();

	private static final String CHAR = "char";
	private static final String VARCHAR = "varchar";
	private static final String INT = "int";
	private static final String TINYINT = "tinyint";
	private static final String SMALLINT = "smallint";
	private static final String BIGINT = "bigint";

	private DBConn() {

	}

	public static DBConn getInstance() {
		return dbconn;
	}

	public Connection getConn() throws ClassNotFoundException, SQLException {
		if (conn == null) {
			Class.forName(DRIVER);
			conn = DriverManager.getConnection(url, userName, password);
			log.info("连接数据库成功!");
		}
		return conn;
	}

	public void closeConn() {
		if (conn != null)
			try {
				log.info("关闭数据库...");
				if (rs != null)
					rs.close();
				conn.close();
			} catch (SQLException e) {
				log.error(e.getMessage(), e);
			}

	}

	public String analyze(String sql) {
		String result = "";
		try {
			sql = sql.trim().toLowerCase();
			Map<String, ColumnInfo> columnInfoMap = new HashMap<String, ColumnInfo>();
			String[] names = null;
			String[] values = null;
			String tableName = null;
			if(sql.startsWith("insert")) { //insert
				int index1 = sql.indexOf("(");
				int index2 = sql.indexOf(")");
				names = sql.substring(index1 + 1, index2).split(",");
				String _sql = StringUtils.substringAfter(sql, "values");
				values = StringUtils.substringBetween(_sql, "(", ")").split(",");
				
				int index3 = sql.indexOf("o");
				tableName = sql.substring(index3 + 1, index1).trim();
			}else if(sql.startsWith("update")) { //update
				String _sql = null;
				if(StringUtils.contains(sql, "where")) {
					_sql = StringUtils.substringBetween(sql, "set", "where");
				}else {
					_sql = StringUtils.substringAfter(sql, "set");
				}
				String[] nameValues = _sql.split(",");
				names = new String[nameValues.length];
				values = new String[nameValues.length];
				for(int i=0;i<nameValues.length;i++) {
					names[i] = StringUtils.substringBefore(nameValues[i], "=").trim();
					values[i] = StringUtils.substringAfter(nameValues[i], "=").trim();
				}
				
				tableName = StringUtils.substringBetween(sql, "update", "set").trim();
			}else {
				return "目前只支持标准的insert/update的sql语句。";
			}

			for (int i = 0; i < names.length; i++) {
				log.debug(names[i] + ":" + values[i]);
				ColumnInfo column = new ColumnInfo();
				column.setName(names[i].trim());
				column.setValue(values[i].trim());
				if ("null".equals(column.getValue()))
					continue;
				columnInfoMap.put(names[i].trim(), column);
			}
			log.debug("tableName:" + tableName);

			try {
				rs = conn.getMetaData().getColumns(null, null, tableName, "%");
				while (rs.next()) {
					String dataName = rs.getString("COLUMN_NAME");
					String dataType = rs.getString("TYPE_NAME");
					int dataSize = rs.getInt("COLUMN_SIZE");
					log.debug("dataName:" + dataName+"  dataType:" + dataType+"  dataSize:" + dataSize);
					if (columnInfoMap.get(dataName.trim()) != null) {
						ColumnInfo column = columnInfoMap.get(dataName.trim());
						column.setType(dataType);
						column.setSize(dataSize);
					}
				}
				rs.close();
				rs = null;
			} catch (SQLException e) {
				log.error(e.getMessage(), e);
				try {
					conn.close();
				} catch (SQLException e1) {
					e1.printStackTrace();
				}
				conn = null;
				return "连接数据库发生错误：" + e.getMessage();
			}

			Map<String, String> errMsgMap = new HashMap<String, String>();
			for (String name : columnInfoMap.keySet()) {
				ColumnInfo column = columnInfoMap.get(name);
				String type = column.getType();
				int size = column.getSize();
				String value = column.getValue();
				if (CHAR.equals(type)) {
					value = value.replaceAll("'", "");
					int lenth = Utils.getRealLength(value);
					if (lenth > size)
						errMsgMap.put(name, populateErrMsg(name, value, lenth, size));
				} else if (VARCHAR.equals(type)) {
					value = value.replaceAll("'", "");
					int lenth = Utils.getRealLength(value);
					if (lenth > size)
						errMsgMap.put(name, populateErrMsg(name, value, lenth, size));
				} else if (INT.equals(type)) {
					int lenth = Utils.getRealLength(value);
					if (lenth > size)
						errMsgMap.put(name, populateErrMsg(name, value, lenth, size));
				} else if (TINYINT.equals(type)) {
					int lenth = Utils.getRealLength(value);
					if (lenth > size)
						errMsgMap.put(name, populateErrMsg(name, value, lenth, size));
				} else if (SMALLINT.equals(type)) {
					int lenth = Utils.getRealLength(value);
					if (lenth > size)
						errMsgMap.put(name, populateErrMsg(name, value, lenth, size));
				} else if (BIGINT.equals(type)) {
					int lenth = Utils.getRealLength(value);
					if (lenth > size)
						errMsgMap.put(name, populateErrMsg(name, value, lenth, size));
				}
			}
			if(errMsgMap.size() > 0) {
				for (String name : errMsgMap.keySet()) {
					result += errMsgMap.get(name) + "\n";
				}
			}else 
				result = "该语句没有分析出截断的值，请检验下您的语句是否正常。";
		
			log.info(result);
		} catch (Exception e) {
			log.error(e.getMessage(), e);
			return "sql语句有错或不符合解析格式：" + e.getMessage();
		}
		return result;
	}

	private String populateErrMsg(String name,String value, int lenth, int size) {
		return "字段【" + name + "】的值"+"{"+value+"}的长度为" + lenth + "超过了该字段最大长度值" + size + "。";
	}

	public static void main(String[] args) throws ClassNotFoundException,
			SQLException {
//		setUrl("jdbc:jtds:sqlserver://222.177.23.145:1433/cqeis4");
//		setUserName("webuser");
//		setPassword("zdsoft_cqjw)@#");
//		dbconn.getConn();
//		// String insert = "insert into
//		// student_info(id,student_name,student_code)
//		// values('123','kobe','001')";
//		String sql = "INSERT INTO student_info ( id, school_id, class_id, student_name, old_name, student_code,"
//				+ " unitive_code, identity_card, sex, birthday, is_local_source, mobile_phone, password, now_state,"
//				+ " leave_sign, isdeleted, is_freshman, updatestamp, enroll_year, class_inner_code, outid, school_district,"
//				+ " economy_state, card_number, meetexam_code, singleton, stayin, boorish, study_mode, native_place, homepage,"
//				+ " email, link_phone, link_address, postalcode, student_recruitment, source_place, region_code, creation_time )"
//				+ " VALUES ( '0000345F5F57456FA0F8BCF210D99999', '8A8980842318F1D801231BD9A5691709', 'FF80808123F9499B012404B5DA8023F9',"
//				+ " 'kobe', '', '20150047', '50200901276930', '500381200312231238', 1, '2003-12-23 0:00:00.000', '0', NULL, NULL, '23', '0', '0', '1', 1255919679894, '2009-2010', NULL, NULL, NULL, NULL, NULL, NULL, 0, 0, 0, '1', '500116', '', '', '13883836258', NULL, '402283', '12', '5001167', NULL, '2009-10-19 10:23:40.733' )";
//		dbconn.analyze(sql);
	}

	public static String getUrl() {
		return url;
	}

	public static void setUrl(String url) {
		DBConn.url = url;
	}

	public static String getUserName() {
		return userName;
	}

	public static void setUserName(String userName) {
		DBConn.userName = userName;
	}

	public static String getPassword() {
		return password;
	}

	public static void setPassword(String password) {
		DBConn.password = password;
	}

}
